﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

using System.Data;
using System.IO;

namespace TravellingService
{
    class DataProvider
    {
        //protected string connectionString = @"Data Source=PRMHUITEDU-PC\SQLEXPRESS;Initial Catalog=TestWebservice;Persist Security Info=True;User ID=hoangminhuit;Password=123456";
        protected string connectionString = @"Data Source=free-sql.BizHostNet.com;Initial Catalog=1325143678;Persist Security Info=True;User ID=1325143678;Password=ryuxavdzt12oaa";
        public SqlConnection connection;
        protected string _error;


        public DataProvider()
        {
            this.connection = new SqlConnection(connectionString);
        }


        private void ReadConfig()
        {
            string path = "config.ini";
            using (StreamReader sr = new StreamReader(path))
            {
                if (sr.Peek() > -1)
                {
                    connectionString = sr.ReadLine();
                }
            }
        }

        public bool Connect()
        {
            try
            {
                connection.Open();
            }
            catch (System.Exception e)
            {
                _error = e.ToString();
                return false;
            }
            return true;
        }

        public void Disconnect()
        {
            connection.Close();
        }
        

#region "Execute_Query"
        //Insert query
        protected bool _ExecuteQuery(string pStrQuery)
        {
            Connect();

            SqlCommand command = new SqlCommand(pStrQuery, this.connection);
            int kq = 0;
            try
            {
                kq = command.ExecuteNonQuery();
            }
            catch (SqlException el)
            {
                _error = el.ToString();
            }
            if (kq <= 0)
            {
                return false;
            }
            return true;
        }

        //Select data and pour into a datatable
        public DataSet _Select(string pStrSelect)
        {
            Connect();
            SqlCommand cmd = new SqlCommand(pStrSelect, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataSet table = new DataSet();
            adap.Fill(table);
            return table;
        }

        public DataSet _Select(string pStrSelect, string paccount)
        {
            Connect();
            SqlCommand cmd = new SqlCommand(pStrSelect, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@account", SqlDbType.NVarChar).Value = paccount;
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            DataSet table = new DataSet();

            adap.Fill(table);
            return table;
        }
#endregion
    }
        
}
